--    Author    : MAYANTING
--    Name      : ADM.RPT_SUST_DLMG_TERM_PRO_LOAF.HQL
--    Functions : 表2：贷款发生额产品期限结构表 
--    Purpose   : 只卡发放
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2018-06-11  MAYANTING           1.CREATE THE PROCEDURE
--
INSERT OVERWRITE TABLE ADM.RPT_SUST_DLMG_TERM_PRO_LOAF PARTITION (DATA_DATE = '#V_DATA_DATE#')
SELECT  AREA.AREA_CODE_4                AS FIN_ORG_DIST
       ,T.FIN_ORG_NO
       ,T.CCY
       ,T.BUSINESS_TYPE
       ,T.BUSINESS_DESC
       ,SUM(T.AMOUNT_0_6M    )/100000000        --6个月（含）以内-贷款余额
       ,SUM(T.WSUM_0_6M   )/100000000           --6个月（含）以内-加权金额
       ,SUM(T.AMOUNT_6_12M   )/100000000        --6个月至1年（含）-贷款余额
       ,SUM(T.WSUM_6_12M  )/100000000           --6个月至1年（含）-加权金额
       ,SUM(T.AMOUNT_1_3Y    )/100000000        --1至3年（含）-贷款余额
       ,SUM(T.WSUM_1_3Y   )/100000000           --1至3年（含）-加权金额
       ,SUM(T.AMOUNT_3_5Y    )/100000000        --3至5年（含）-贷款余额
       ,SUM(T.WSUM_3_5Y   )/100000000           --3至5年（含）-加权金额
       ,SUM(T.AMOUNT_5_10Y   )/100000000        --5至10年（含）-贷款余额
       ,SUM(T.WSUM_5_10Y  )/100000000           --5至10年（含）-加权金额
       ,SUM(T.AMOUNT_10_999Y )/100000000        --10年以上-贷款余额
       ,SUM(T.WSUM_10_999Y)/100000000           --10年以上-加权金额
       ,SUM(T.AMOUNT_ALL     )/100000000        --所有期限-贷款余额
       ,SUM(T.WSUM_ALL    )/100000000           --所有期限-加权金额
FROM (
    -- 个人+机构
    SELECT
     FSE.FIN_ORG_NO                                                                AS FIN_ORG_NO
    ,FSE.CCY                                                                       AS CCY
    ,FSE.CLIENT_TYPE                                                               AS BUSINESS_TYPE
    ,CASE WHEN FSE.CLIENT_TYPE='1' THEN '个人'                                     
          WHEN FSE.CLIENT_TYPE='0' THEN '机构' END                                 AS BUSINESS_DESC
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='1' THEN FSE.OCCUR_AMOUNT  ELSE 0 END,0)    AS AMOUNT_0_6M         --6个月（含）以内-贷款余额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='1' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_0_6M           --6个月（含）以内-加权金额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='2' THEN FSE.OCCUR_AMOUNT  ELSE 0 END,0)    AS AMOUNT_6_12M        --6个月至1年（含）-贷款余额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='2' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_6_12M          --6个月至1年（含）-加权金额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='3' THEN FSE.OCCUR_AMOUNT  ELSE 0 END,0)    AS AMOUNT_1_3Y         --1至3年（含）-贷款余额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='3' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_1_3Y           --1至3年（含）-加权金额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='4' THEN FSE.OCCUR_AMOUNT  ELSE 0 END,0)    AS AMOUNT_3_5Y         --3至5年（含）-贷款余额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='4' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_3_5Y           --3至5年（含）-加权金额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='5' THEN FSE.OCCUR_AMOUNT  ELSE 0 END,0)    AS AMOUNT_5_10Y        --5至10年（含）-贷款余额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='5' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_5_10Y          --5至10年（含）-加权金额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='6' THEN FSE.OCCUR_AMOUNT  ELSE 0 END,0)    AS AMOUNT_10_999Y      --10年以上-贷款余额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='6' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_10_999Y        --10年以上-加权金额
    ,COALESCE(FSE.OCCUR_AMOUNT,0)                                                  AS AMOUNT_ALL          --所有期限-贷款余额
    ,COALESCE(FSE.WSUM_AMOUNT,0)                                                   AS WSUM_ALL            --所有期限-加权金额
    FROM (SELECT * FROM EDW.DS_LOAF_SUM WHERE DATA_DATE='#V_DATA_DATE#'AND DRAWDOWN_OR_REPAY='1') FSE -- 发放

    UNION ALL

    -- 个人_普通贷款
    SELECT
     FSE.FIN_ORG_NO                                                                AS FIN_ORG_NO
    ,FSE.CCY                                                                       AS CCY
    ,'11'                                                                          AS BUSINESS_TYPE
    ,'普通贷款'                                                                    AS BUSINESS_DESC
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='1' THEN FSE.OCCUR_AMOUNT  ELSE 0 END,0)    AS AMOUNT_0_6M         --6个月（含）以内-贷款余额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='1' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_0_6M           --6个月（含）以内-加权金额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='2' THEN FSE.OCCUR_AMOUNT  ELSE 0 END,0)    AS AMOUNT_6_12M        --6个月至1年（含）-贷款余额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='2' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_6_12M          --6个月至1年（含）-加权金额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='3' THEN FSE.OCCUR_AMOUNT  ELSE 0 END,0)    AS AMOUNT_1_3Y         --1至3年（含）-贷款余额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='3' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_1_3Y           --1至3年（含）-加权金额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='4' THEN FSE.OCCUR_AMOUNT  ELSE 0 END,0)    AS AMOUNT_3_5Y         --3至5年（含）-贷款余额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='4' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_3_5Y           --3至5年（含）-加权金额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='5' THEN FSE.OCCUR_AMOUNT  ELSE 0 END,0)    AS AMOUNT_5_10Y        --5至10年（含）-贷款余额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='5' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_5_10Y          --5至10年（含）-加权金额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='6' THEN FSE.OCCUR_AMOUNT  ELSE 0 END,0)    AS AMOUNT_10_999Y      --10年以上-贷款余额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='6' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_10_999Y        --10年以上-加权金额
    ,COALESCE(FSE.OCCUR_AMOUNT,0)                                                  AS AMOUNT_ALL          --所有期限-贷款余额
    ,COALESCE(FSE.WSUM_AMOUNT,0)                                                   AS WSUM_ALL            --所有期限-加权金额
    FROM (SELECT * FROM EDW.DS_LOAF_SUM WHERE CLIENT_TYPE='1' AND BUSINESS_TYPE IN ('F0211','F0212','F02131','F02132','F0219','F02201','F02202')AND DATA_DATE='#V_DATA_DATE#' AND DRAWDOWN_OR_REPAY='1') FSE -- 发放

UNION ALL

    -- 个人_消费贷款
    SELECT
     FSE.FIN_ORG_NO                                                                AS FIN_ORG_NO
    ,FSE.CCY                                                                       AS CCY
    ,'111'                                                                         AS BUSINESS_TYPE
    ,'消费贷款'                                                                    AS BUSINESS_DESC
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='1' THEN FSE.OCCUR_AMOUNT  ELSE 0 END,0)    AS AMOUNT_0_6M         --6个月（含）以内-贷款余额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='1' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_0_6M           --6个月（含）以内-加权金额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='2' THEN FSE.OCCUR_AMOUNT  ELSE 0 END,0)    AS AMOUNT_6_12M        --6个月至1年（含）-贷款余额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='2' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_6_12M          --6个月至1年（含）-加权金额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='3' THEN FSE.OCCUR_AMOUNT  ELSE 0 END,0)    AS AMOUNT_1_3Y         --1至3年（含）-贷款余额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='3' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_1_3Y           --1至3年（含）-加权金额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='4' THEN FSE.OCCUR_AMOUNT  ELSE 0 END,0)    AS AMOUNT_3_5Y         --3至5年（含）-贷款余额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='4' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_3_5Y           --3至5年（含）-加权金额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='5' THEN FSE.OCCUR_AMOUNT  ELSE 0 END,0)    AS AMOUNT_5_10Y        --5至10年（含）-贷款余额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='5' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_5_10Y          --5至10年（含）-加权金额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='6' THEN FSE.OCCUR_AMOUNT  ELSE 0 END,0)    AS AMOUNT_10_999Y      --10年以上-贷款余额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='6' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_10_999Y        --10年以上-加权金额
    ,COALESCE(FSE.OCCUR_AMOUNT,0)                                                  AS AMOUNT_ALL          --所有期限-贷款余额
    ,COALESCE(FSE.WSUM_AMOUNT,0)                                                   AS WSUM_ALL            --所有期限-加权金额
    FROM (SELECT * FROM EDW.DS_LOAF_SUM WHERE CLIENT_TYPE='1' AND BUSINESS_TYPE IN ('F0211','F0212','F02131','F02132','F0219')AND DATA_DATE='#V_DATA_DATE#' AND DRAWDOWN_OR_REPAY='1' ) FSE -- 发放

    UNION ALL

    -- 个人_个人住房贷款+个人汽车消费贷款+其他消费贷款+经营贷款
    SELECT
     FSE.FIN_ORG_NO                                                                AS FIN_ORG_NO
    ,FSE.CCY                                                                       AS CCY
    ,CASE WHEN FSE.BUSINESS_TYPE='F0211' THEN '1111' -- 个人住房贷款
          WHEN FSE.BUSINESS_TYPE='F0212' THEN '1112' -- 个人汽车消费贷款
          WHEN FSE.BUSINESS_TYPE='F0219' THEN '1114' -- 其他消费贷款
          WHEN FSE.BUSINESS_TYPE IN ('F02201','F02202') THEN '112'  -- 经营贷款
     END                                                                           AS BUSINESS_TYPE
    ,CASE WHEN FSE.BUSINESS_TYPE='F0211' THEN '个人住房贷款'
          WHEN FSE.BUSINESS_TYPE='F0212' THEN '个人汽车消费贷款'
          WHEN FSE.BUSINESS_TYPE='F0219' THEN '其他消费贷款'
          WHEN FSE.BUSINESS_TYPE IN ('F02201','F02202') THEN '经营贷款'
     END                                                                           AS BUSINESS_DESC
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='1' THEN FSE.OCCUR_AMOUNT ELSE 0 END,0)     AS AMOUNT_0_6M         --6个月（含）以内-贷款余额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='1' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_0_6M           --6个月（含）以内-加权金额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='2' THEN FSE.OCCUR_AMOUNT ELSE 0 END,0)     AS AMOUNT_6_12M        --6个月至1年（含）-贷款余额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='2' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_6_12M          --6个月至1年（含）-加权金额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='3' THEN FSE.OCCUR_AMOUNT ELSE 0 END,0)     AS AMOUNT_1_3Y         --1至3年（含）-贷款余额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='3' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_1_3Y           --1至3年（含）-加权金额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='4' THEN FSE.OCCUR_AMOUNT ELSE 0 END,0)     AS AMOUNT_3_5Y         --3至5年（含）-贷款余额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='4' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_3_5Y           --3至5年（含）-加权金额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='5' THEN FSE.OCCUR_AMOUNT ELSE 0 END,0)     AS AMOUNT_5_10Y        --5至10年（含）-贷款余额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='5' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_5_10Y          --5至10年（含）-加权金额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='6' THEN FSE.OCCUR_AMOUNT ELSE 0 END,0)     AS AMOUNT_10_999Y      --10年以上-贷款余额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='6' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_10_999Y        --10年以上-加权金额
    ,COALESCE(FSE.OCCUR_AMOUNT,0)                                                  AS AMOUNT_ALL          --所有期限-贷款余额
    ,COALESCE(FSE.WSUM_AMOUNT,0)                                                   AS WSUM_ALL            --所有期限-加权金额
    FROM (SELECT * FROM EDW.DS_LOAF_SUM WHERE CLIENT_TYPE='1' AND BUSINESS_TYPE IN ('F0211','F0212','F02201','F02202','F0219')AND DATA_DATE='#V_DATA_DATE#' AND DRAWDOWN_OR_REPAY='1') FSE -- 发放

    UNION ALL

    -- 个人_助学贷款
    SELECT
     FSE.FIN_ORG_NO                                                                AS FIN_ORG_NO
    ,FSE.CCY                                                                       AS CCY
    ,'1113'                                                                        AS BUSINESS_TYPE
    ,'助学贷款'                                                                    AS BUSINESS_DESC
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='1' THEN FSE.OCCUR_AMOUNT  ELSE 0 END,0)    AS AMOUNT_0_6M         --6个月（含）以内-贷款余额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='1' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_0_6M           --6个月（含）以内-加权金额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='2' THEN FSE.OCCUR_AMOUNT  ELSE 0 END,0)    AS AMOUNT_6_12M        --6个月至1年（含）-贷款余额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='2' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_6_12M          --6个月至1年（含）-加权金额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='3' THEN FSE.OCCUR_AMOUNT  ELSE 0 END,0)    AS AMOUNT_1_3Y         --1至3年（含）-贷款余额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='3' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_1_3Y           --1至3年（含）-加权金额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='4' THEN FSE.OCCUR_AMOUNT  ELSE 0 END,0)    AS AMOUNT_3_5Y         --3至5年（含）-贷款余额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='4' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_3_5Y           --3至5年（含）-加权金额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='5' THEN FSE.OCCUR_AMOUNT  ELSE 0 END,0)    AS AMOUNT_5_10Y        --5至10年（含）-贷款余额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='5' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_5_10Y          --5至10年（含）-加权金额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='6' THEN FSE.OCCUR_AMOUNT  ELSE 0 END,0)    AS AMOUNT_10_999Y      --10年以上-贷款余额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='6' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_10_999Y        --10年以上-加权金额
    ,COALESCE(FSE.OCCUR_AMOUNT,0)                                                  AS AMOUNT_ALL          --所有期限-贷款余额
    ,COALESCE(FSE.WSUM_AMOUNT,0)                                                   AS WSUM_ALL            --所有期限-加权金额
    FROM (SELECT * FROM EDW.DS_LOAF_SUM WHERE CLIENT_TYPE='1' AND BUSINESS_TYPE IN ('F02131','F02132')AND DATA_DATE='#V_DATA_DATE#' AND DRAWDOWN_OR_REPAY='1') FSE -- 发放

UNION ALL

    -- 机构_普通贷款
    SELECT
     FSE.FIN_ORG_NO                                                                AS FIN_ORG_NO
    ,FSE.CCY                                                                       AS CCY
    ,'21'                                                                          AS BUSINESS_TYPE
    ,'普通贷款'                                                                    AS BUSINESS_DESC
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='1' THEN FSE.OCCUR_AMOUNT  ELSE 0 END,0)    AS AMOUNT_0_6M         --6个月（含）以内-贷款余额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='1' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_0_6M           --6个月（含）以内-加权金额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='2' THEN FSE.OCCUR_AMOUNT  ELSE 0 END,0)    AS AMOUNT_6_12M        --6个月至1年（含）-贷款余额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='2' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_6_12M          --6个月至1年（含）-加权金额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='3' THEN FSE.OCCUR_AMOUNT  ELSE 0 END,0)    AS AMOUNT_1_3Y         --1至3年（含）-贷款余额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='3' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_1_3Y           --1至3年（含）-加权金额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='4' THEN FSE.OCCUR_AMOUNT  ELSE 0 END,0)    AS AMOUNT_3_5Y         --3至5年（含）-贷款余额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='4' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_3_5Y           --3至5年（含）-加权金额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='5' THEN FSE.OCCUR_AMOUNT  ELSE 0 END,0)    AS AMOUNT_5_10Y        --5至10年（含）-贷款余额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='5' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_5_10Y          --5至10年（含）-加权金额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='6' THEN FSE.OCCUR_AMOUNT  ELSE 0 END,0)    AS AMOUNT_10_999Y      --10年以上-贷款余额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='6' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_10_999Y        --10年以上-加权金额
    ,COALESCE(FSE.OCCUR_AMOUNT,0)                                                  AS AMOUNT_ALL          --所有期限-贷款余额
    ,COALESCE(FSE.WSUM_AMOUNT,0)                                                   AS WSUM_ALL            --所有期限-加权金额
    FROM (SELECT * FROM EDW.DS_LOAF_SUM WHERE CLIENT_TYPE='0' AND BUSINESS_TYPE IN ('F02203','F023')AND DATA_DATE='#V_DATA_DATE#' AND DRAWDOWN_OR_REPAY='1') FSE -- 发放

    UNION ALL

    -- 机构_贸易融资
    SELECT
     FSE.FIN_ORG_NO                                                                AS FIN_ORG_NO
    ,FSE.CCY                                                                       AS CCY
    ,'22'                                                                          AS BUSINESS_TYPE
    ,'贸易融资'                                                                    AS BUSINESS_DESC
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='1' THEN FSE.OCCUR_AMOUNT  ELSE 0 END,0)    AS AMOUNT_0_6M         --6个月（含）以内-贷款余额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='1' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_0_6M           --6个月（含）以内-加权金额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='2' THEN FSE.OCCUR_AMOUNT  ELSE 0 END,0)    AS AMOUNT_6_12M        --6个月至1年（含）-贷款余额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='2' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_6_12M          --6个月至1年（含）-加权金额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='3' THEN FSE.OCCUR_AMOUNT  ELSE 0 END,0)    AS AMOUNT_1_3Y         --1至3年（含）-贷款余额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='3' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_1_3Y           --1至3年（含）-加权金额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='4' THEN FSE.OCCUR_AMOUNT  ELSE 0 END,0)    AS AMOUNT_3_5Y         --3至5年（含）-贷款余额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='4' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_3_5Y           --3至5年（含）-加权金额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='5' THEN FSE.OCCUR_AMOUNT  ELSE 0 END,0)    AS AMOUNT_5_10Y        --5至10年（含）-贷款余额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='5' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_5_10Y          --5至10年（含）-加权金额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='6' THEN FSE.OCCUR_AMOUNT  ELSE 0 END,0)    AS AMOUNT_10_999Y      --10年以上-贷款余额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='6' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_10_999Y        --10年以上-加权金额
    ,COALESCE(FSE.OCCUR_AMOUNT,0)                                                  AS AMOUNT_ALL          --所有期限-贷款余额
    ,COALESCE(FSE.WSUM_AMOUNT,0)                                                   AS WSUM_ALL            --所有期限-加权金额
    FROM (SELECT * FROM EDW.DS_LOAF_SUM WHERE CLIENT_TYPE='0' AND BUSINESS_TYPE IN ('F081','F082')AND DATA_DATE='#V_DATA_DATE#' AND DRAWDOWN_OR_REPAY='1') FSE -- 发放

    UNION ALL

    -- 机构_经营贷款+固定资产贷款+融资租赁+转贷款+并购贷款
    SELECT
     FSE.FIN_ORG_NO                                                                AS FIN_ORG_NO
    ,FSE.CCY                                                                       AS CCY
    ,CASE WHEN FSE.BUSINESS_TYPE='F02203'  THEN '211'
          WHEN FSE.BUSINESS_TYPE='F023'    THEN '212'
          WHEN FSE.BUSINESS_TYPE='F09'     THEN '23'
          WHEN FSE.BUSINESS_TYPE='F11'     THEN '24'
          WHEN FSE.BUSINESS_TYPE='F12'     THEN '25'
     END                                                                           AS BUSINESS_TYPE
    ,CASE WHEN FSE.BUSINESS_TYPE='F02203' THEN '经营贷款'
          WHEN FSE.BUSINESS_TYPE='F023'   THEN '固定资产贷款'
          WHEN FSE.BUSINESS_TYPE='F09'    THEN '融资租赁'
          WHEN FSE.BUSINESS_TYPE='F11'    THEN '转贷款'
          WHEN FSE.BUSINESS_TYPE='F12'    THEN '并购贷款'
     END                                                                           AS BUSINESS_DESC
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='1' THEN FSE.OCCUR_AMOUNT  ELSE 0 END,0)    AS AMOUNT_0_6M         --6个月（含）以内-贷款余额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='1' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_0_6M           --6个月（含）以内-加权金额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='2' THEN FSE.OCCUR_AMOUNT  ELSE 0 END,0)    AS AMOUNT_6_12M        --6个月至1年（含）-贷款余额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='2' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_6_12M          --6个月至1年（含）-加权金额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='3' THEN FSE.OCCUR_AMOUNT  ELSE 0 END,0)    AS AMOUNT_1_3Y         --1至3年（含）-贷款余额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='3' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_1_3Y           --1至3年（含）-加权金额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='4' THEN FSE.OCCUR_AMOUNT  ELSE 0 END,0)    AS AMOUNT_3_5Y         --3至5年（含）-贷款余额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='4' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_3_5Y           --3至5年（含）-加权金额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='5' THEN FSE.OCCUR_AMOUNT  ELSE 0 END,0)    AS AMOUNT_5_10Y        --5至10年（含）-贷款余额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='5' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_5_10Y          --5至10年（含）-加权金额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='6' THEN FSE.OCCUR_AMOUNT  ELSE 0 END,0)    AS AMOUNT_10_999Y      --10年以上-贷款余额
    ,COALESCE(CASE WHEN FSE.TERM_CODE ='6' THEN FSE.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_10_999Y        --10年以上-加权金额
    ,COALESCE(FSE.OCCUR_AMOUNT,0)                                                  AS AMOUNT_ALL          --所有期限-贷款余额
    ,COALESCE(FSE.WSUM_AMOUNT,0)                                                   AS WSUM_ALL            --所有期限-加权金额
    FROM (SELECT * FROM EDW.DS_LOAF_SUM WHERE CLIENT_TYPE='0' AND BUSINESS_TYPE IN ('F02203','F023','F09','F11','F12')AND DATA_DATE='#V_DATA_DATE#' AND DRAWDOWN_OR_REPAY='1') FSE -- 发放

    UNION ALL

    -- 合计
    SELECT
     LOAF.FIN_ORG_NO                                                                 AS FIN_ORG_NO
    ,LOAF.CCY                                                                        AS CCY
    ,'3'                                                                             AS BUSINESS_TYPE
    ,'合计'                                                                          AS BUSINESS_DESC
    ,COALESCE(CASE WHEN LOAF.TERM_CODE ='1' THEN LOAF.OCCUR_AMOUNT  ELSE 0 END,0)    AS AMOUNT_0_6M         --6个月（含）以内-贷款余额
    ,COALESCE(CASE WHEN LOAF.TERM_CODE ='1' THEN LOAF.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_0_6M           --6个月（含）以内-加权金额
    ,COALESCE(CASE WHEN LOAF.TERM_CODE ='2' THEN LOAF.OCCUR_AMOUNT  ELSE 0 END,0)    AS AMOUNT_6_12M        --6个月至1年（含）-贷款余额
    ,COALESCE(CASE WHEN LOAF.TERM_CODE ='2' THEN LOAF.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_6_12M          --6个月至1年（含）-加权金额
    ,COALESCE(CASE WHEN LOAF.TERM_CODE ='3' THEN LOAF.OCCUR_AMOUNT  ELSE 0 END,0)    AS AMOUNT_1_3Y         --1至3年（含）-贷款余额
    ,COALESCE(CASE WHEN LOAF.TERM_CODE ='3' THEN LOAF.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_1_3Y           --1至3年（含）-加权金额
    ,COALESCE(CASE WHEN LOAF.TERM_CODE ='4' THEN LOAF.OCCUR_AMOUNT  ELSE 0 END,0)    AS AMOUNT_3_5Y         --3至5年（含）-贷款余额
    ,COALESCE(CASE WHEN LOAF.TERM_CODE ='4' THEN LOAF.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_3_5Y           --3至5年（含）-加权金额
    ,COALESCE(CASE WHEN LOAF.TERM_CODE ='5' THEN LOAF.OCCUR_AMOUNT  ELSE 0 END,0)    AS AMOUNT_5_10Y        --5至10年（含）-贷款余额
    ,COALESCE(CASE WHEN LOAF.TERM_CODE ='5' THEN LOAF.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_5_10Y          --5至10年（含）-加权金额
    ,COALESCE(CASE WHEN LOAF.TERM_CODE ='6' THEN LOAF.OCCUR_AMOUNT  ELSE 0 END,0)    AS AMOUNT_10_999Y      --10年以上-贷款余额
    ,COALESCE(CASE WHEN LOAF.TERM_CODE ='6' THEN LOAF.WSUM_AMOUNT   ELSE 0 END,0)    AS WSUM_10_999Y        --10年以上-加权金额
    ,COALESCE(LOAF.OCCUR_AMOUNT,0)                                                   AS AMOUNT_ALL          --所有期限-贷款余额
    ,COALESCE(LOAF.WSUM_AMOUNT,0)                                                    AS WSUM_ALL            --所有期限-加权金额
    FROM (SELECT * FROM EDW.DS_LOAF_SUM WHERE DATA_DATE='#V_DATA_DATE#' AND DRAWDOWN_OR_REPAY='1') LOAF -- 发放
   )T
   -- 与地区表最细级关联
  LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON T.FIN_ORG_NO=REAL.ORG_ID AND '#V_DATA_DATE#' BETWEEN REAL.START_DATE AND REAL.END_DATE
  LEFT JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '#V_DATA_DATE#' BETWEEN AREA.START_DATE AND AREA.END_DATE
  GROUP BY   AREA.AREA_CODE_4
            ,T.FIN_ORG_NO
            ,T.CCY
            ,T.BUSINESS_TYPE
            ,T.BUSINESS_DESC
;